﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using XQ.RequestOfficeSupplies.DAL.Model;
using XQ.RequestOfficeSupplies.DAL.Model.Business;
using XQ.RequestOfficeSupplies.DAL.Models;
using Newtonsoft.Json;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Xceed.Document.NET;
using Xceed.Words.NET;
using System.IO;
using XQ.RequestOfficeSupplies.DAL.Model.Sys;
using XQ.RequestOfficeSupplies.DAL.DAL.Common;

namespace XQ.RequestOfficeSupplies.BLL.Export
{
    public class ExportBLL
    {
        //WaterBillDal waterBillDal = new WaterBillDal();
        //ElectricityBillDal electricityBillDal = new ElectricityBillDal();
        //RentBillDal rentBillDal = new RentBillDal();

        ///// <summary>
        ///// 导出水费清单
        ///// </summary>
        ///// <param name="ssm"></param>
        ///// <param name="page"></param>
        ///// <param name="token"></param>
        ///// <returns></returns>
        //public string ExportWaterBill(WaterBillSearchModel ssm, Pagination page, TokenModel token)
        //{
        //    ssm.pageSize = 10000;
        //    List<WaterBillReturnModel> waterBills = waterBillDal.GetListByOpenid(ssm, page, token);
        //    try
        //    {
        //        //Excel列数组
        //        string[] excelCols = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN" };
        //        List<KeyNameModel> cols = new List<KeyNameModel>();
        //        cols.Add(new KeyNameModel { Key = "SortIndex", Name = "序号" });
        //        cols.Add(new KeyNameModel { Key = "ContractNo", Name = "合同编号" });
        //        cols.Add(new KeyNameModel { Key = "LinkMan", Name = "乙方/承租人" });
        //        cols.Add(new KeyNameModel { Key = "Address", Name = "地址" });
        //        cols.Add(new KeyNameModel { Key = "Year", Name = "年份" });
        //        cols.Add(new KeyNameModel { Key = "Month", Name = "月份" }); 
        //        cols.Add(new KeyNameModel { Key = "money", Name = "金额" });
        //        cols.Add(new KeyNameModel { Key = "lastMonthMeterReading", Name = "上次示数" });
        //        cols.Add(new KeyNameModel { Key = "currentMonthMeterReading", Name = "本次示数" });
        //        cols.Add(new KeyNameModel { Key = "IsPay", Name = "是否缴费" });
        //        cols.Add(new KeyNameModel { Key = "isAudit", Name = "是否审核" });
        //        cols.Add(new KeyNameModel { Key = "Remark", Name = "备注" });

        //        DirectoryInfo boot = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath("/"));
        //        string path = boot + "\\Upload\\Output\\";
        //        var time = token.UID + DateTime.Now.ToString("yyyyMMddHHmmss");
        //        string filename = "水费账单(" + time + ").xlsx";
        //        FileInfo file = new FileInfo(Path.Combine(path, filename));
        //        file.Delete();
        //        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial;
        //        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
        //        using (var package = new ExcelPackage(file))
        //        {
        //            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("水费账单");
        //            worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            for (int i = 0; i < cols.Count; i++)
        //            {
        //                worksheet.Cells[1, i + 1].Value = cols[i].Name;
        //                worksheet.Cells[1, i + 1].Style.Font.Bold = true;
        //                worksheet.Cells[1, i + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        //            }

        //            var row = 2;
        //            string valueStr = string.Empty;
        //            foreach (var list in waterBills)
        //            {
        //                for (int j = 0; j < cols.Count; j++)
        //                {
        //                    valueStr = string.Empty;
        //                    switch (cols[j].Key)
        //                    {
        //                        case "ContractNo":
        //                            valueStr = list.ContractNo;
        //                            break;
        //                        case "LinkMan":
        //                            valueStr = list.LinkMan;
        //                            break;
        //                        case "Address":
        //                            valueStr = list.Address;
        //                            break;
        //                        case "Year":
        //                            valueStr = list.year.ToString();
        //                            break;
        //                        case "Month":
        //                            valueStr = list.month.ToString();
        //                            break;
        //                        case "money":
        //                            valueStr = list.money.ToString();
        //                            break;
        //                        case "lastMonthMeterReading":
        //                            valueStr = list.lastMonthMeterReading.ToString();
        //                            break;
        //                        case "currentMonthMeterReading":
        //                            valueStr = list.currentMonthMeterReading.ToString();
        //                            break;
        //                        case "IsPay":
        //                            valueStr = list.IsPay == true ? "是" : "否";
        //                            break;
        //                        case "isAudit":
        //                            valueStr = list.isAudit == true ? "是" : "否";
        //                            break;
        //                        case "Remark":
        //                            valueStr = list.remark;
        //                            break;
        //                        //排序
        //                        case "SortIndex":
        //                            valueStr = (row - 1).ToString();
        //                            break;
        //                    }
        //                    worksheet.Cells[excelCols[j] + row].Value = valueStr;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Left.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Right.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Top.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        //                }
        //                row++;
        //            }
        //                package.Save();
        //        }

        //        return "/Upload/Output/" + filename;
        //    }
        //    catch (Exception ex)
        //    {
        //        return ex.Message;
        //    }
        //}

        ///// <summary>
        ///// 导出电费清单
        ///// </summary>
        ///// <param name="ssm"></param>
        ///// <param name="page"></param>
        ///// <param name="token"></param>
        ///// <returns></returns>
        //public string ExportElectricityBill(ElectricityBillSearchModel ssm, Pagination page, TokenModel token)
        //{
        //    ssm.pageSize = 10000;
        //    List<ElectricityBillReturnModel> electricityBills = electricityBillDal.GetListByOpenid(ssm, page, token);
        //    LogBll.Instance.WriteLog(LogBll.LogType.Excel, "", token.UID, "导出电费账单", "", "", "", "ExportElectricityBill:" + JsonConvert.SerializeObject(electricityBills));
        //    try
        //    {
        //        //Excel列数组
        //        string[] excelCols = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN" };
        //        List<KeyNameModel> cols = new List<KeyNameModel>();
        //        cols.Add(new KeyNameModel { Key = "SortIndex", Name = "序号" });
        //        cols.Add(new KeyNameModel { Key = "ContractNo", Name = "合同编号" });
        //        cols.Add(new KeyNameModel { Key = "LinkMan", Name = "乙方/承租人" });
        //        cols.Add(new KeyNameModel { Key = "Address", Name = "地址" });
        //        cols.Add(new KeyNameModel { Key = "Year", Name = "年份" });
        //        cols.Add(new KeyNameModel { Key = "Month", Name = "月份" });
        //        cols.Add(new KeyNameModel { Key = "money", Name = "金额" });
        //        cols.Add(new KeyNameModel { Key = "lastMonthMeterReading", Name = "上次示数" });
        //        cols.Add(new KeyNameModel { Key = "currentMonthMeterReading", Name = "本次示数" });
        //        cols.Add(new KeyNameModel { Key = "IsPay", Name = "是否缴费" });
        //        cols.Add(new KeyNameModel { Key = "isAudit", Name = "是否审核" });
        //        cols.Add(new KeyNameModel { Key = "Remark", Name = "备注" });

        //        DirectoryInfo boot = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath("/"));
        //        string path = boot + "\\Upload\\Output\\";
        //        var time = token.UID + DateTime.Now.ToString("yyyyMMddHHmmss");
        //        string filename = "电费账单(" + time + ").xlsx";
        //        FileInfo file = new FileInfo(Path.Combine(path, filename));
        //        file.Delete();
        //        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial;
        //        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
        //        using (var package = new ExcelPackage(file))
        //        {
        //            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("电费账单");
        //            worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            for (int i = 0; i < cols.Count; i++)
        //            {
        //                worksheet.Cells[1, i + 1].Value = cols[i].Name;
        //                worksheet.Cells[1, i + 1].Style.Font.Bold = true;
        //                worksheet.Cells[1, i + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        //            }

        //            var row = 2;
        //            string valueStr = string.Empty;
        //            foreach (var list in electricityBills)
        //            {
        //                for (int j = 0; j < cols.Count; j++)
        //                {
        //                    valueStr = string.Empty;
        //                    switch (cols[j].Key)
        //                    {
        //                        case "ContractNo":
        //                            valueStr = list.ContractNo;
        //                            break;
        //                        case "LinkMan":
        //                            valueStr = list.LinkMan;
        //                            break;
        //                        case "Address":
        //                            valueStr = list.Address;
        //                            break;
        //                        case "Year":
        //                            valueStr = list.year.ToString();
        //                            break;
        //                        case "Month":
        //                            valueStr = list.month.ToString();
        //                            break;
        //                        case "money":
        //                            valueStr = list.money.ToString();
        //                            break;
        //                        case "lastMonthMeterReading":
        //                            valueStr = list.lastMonthMeterReading.ToString();
        //                            break;
        //                        case "currentMonthMeterReading":
        //                            valueStr = list.currentMonthMeterReading.ToString();
        //                            break;
        //                        case "IsPay":
        //                            valueStr = list.IsPay == true ? "是" : "否";
        //                            break;
        //                        case "isAudit":
        //                            valueStr = list.isAudit == true ? "是" : "否";
        //                            break;
        //                        case "Remark":
        //                            valueStr = list.remark;
        //                            break;
        //                        //排序
        //                        case "SortIndex":
        //                            valueStr = (row - 1).ToString();
        //                            break;
        //                    }
        //                    worksheet.Cells[excelCols[j] + row].Value = valueStr;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Left.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Right.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Top.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        //                }
        //                row++;
        //            }
        //            package.Save();
        //        }

        //        return "/Upload/Output/" + filename;
        //    }
        //    catch (Exception ex)
        //    {
        //        return ex.Message;
        //    }
        //}

        ///// <summary>
        ///// 导出租金账单
        ///// </summary>
        ///// <param name="ssm"></param>
        ///// <param name="page"></param>
        ///// <param name="token"></param>
        ///// <returns></returns>
        //public string ExportRentBill(RentBillSearchModel ssm, Pagination page, TokenModel token)
        //{
        //    ssm.pageSize = 10000;
        //    List<RentBillReturnModel> rentBills = rentBillDal.GetList(ssm, page, token);
        //    try
        //    {
        //        //Excel列数组
        //        string[] excelCols = new string[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN" };
        //        List<KeyNameModel> cols = new List<KeyNameModel>();
        //        cols.Add(new KeyNameModel { Key = "SortIndex", Name = "序号" });
        //        cols.Add(new KeyNameModel { Key = "ContractNo", Name = "合同编号" });
        //        cols.Add(new KeyNameModel { Key = "LinkMan", Name = "乙方/承租人" });
        //        cols.Add(new KeyNameModel { Key = "billingPeriod", Name = "账单所属期限" });
        //        cols.Add(new KeyNameModel { Key = "PayYear", Name = "年份" });
        //        cols.Add(new KeyNameModel { Key = "PayMonth", Name = "月份" });
        //        cols.Add(new KeyNameModel { Key = "rent", Name = "金额" });
        //        cols.Add(new KeyNameModel { Key = "IsPay", Name = "是否缴费" });
        //        cols.Add(new KeyNameModel { Key = "isAudit", Name = "是否审核" });
        //        cols.Add(new KeyNameModel { Key = "Remark", Name = "备注" });

        //        DirectoryInfo boot = new DirectoryInfo(System.Web.HttpContext.Current.Server.MapPath("/"));
        //        string path = boot + "\\Upload\\Output\\";
        //        var time = token.UID + DateTime.Now.ToString("yyyyMMddHHmmss");
        //        string filename = "电费账单(" + time + ").xlsx";
        //        FileInfo file = new FileInfo(Path.Combine(path, filename));
        //        file.Delete();
        //        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.Commercial;
        //        ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
        //        using (var package = new ExcelPackage(file))
        //        {
        //            ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("电费账单");
        //            worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
        //            for (int i = 0; i < cols.Count; i++)
        //            {
        //                worksheet.Cells[1, i + 1].Value = cols[i].Name;
        //                worksheet.Cells[1, i + 1].Style.Font.Bold = true;
        //                worksheet.Cells[1, i + 1].Style.Border.Left.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Right.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Top.Style = ExcelBorderStyle.Thin;
        //                worksheet.Cells[1, i + 1].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        //            }

        //            var row = 2;
        //            string valueStr = string.Empty;
        //            foreach (var list in rentBills)
        //            {
        //                for (int j = 0; j < cols.Count; j++)
        //                {
        //                    valueStr = string.Empty;
        //                    switch (cols[j].Key)
        //                    {
        //                        case "ContractNo":
        //                            valueStr = list.ContractNo;
        //                            break;
        //                        case "LinkMan":
        //                            valueStr = list.LinkMan;
        //                            break;
        //                        case "billingPeriod":
        //                            valueStr = list.billingPeriod;
        //                            break;
        //                        case "PayYear":
        //                            valueStr = list.PayYear.ToString();
        //                            break;
        //                        case "PayMonth":
        //                            valueStr = list.PayMonth.ToString();
        //                            break;
        //                        case "rent":
        //                            valueStr = list.rent.ToString();
        //                            break;
        //                        case "IsPay":
        //                            valueStr = list.isPay == true ? "是" : "否";
        //                            break;
        //                        case "isAudit":
        //                            valueStr = list.isAudit == true ? "是" : "否";
        //                            break;
        //                        case "Remark":
        //                            valueStr = list.remark;
        //                            break;
        //                        //排序
        //                        case "SortIndex":
        //                            valueStr = (row - 1).ToString();
        //                            break;
        //                    }
        //                    worksheet.Cells[excelCols[j] + row].Value = valueStr;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Left.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Right.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Top.Style = ExcelBorderStyle.Thin;
        //                    worksheet.Cells[excelCols[j] + row].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
        //                }
        //                row++;
        //            }
        //            package.Save();
        //        }

        //        return "/Upload/Output/" + filename;
        //    }
        //    catch (Exception ex)
        //    {
        //        return ex.Message;
        //    }
        //}

    }
}
